﻿using System;
using System.Data;
using System.Collections;
using System.Text;
using System.Data.SqlClient;

using RedGlove.DBUtility;
using RGP.IDAL;

namespace RGP.SQLServerDAL
{
    public class UserGroup : IUserGroup
    {
        public UserGroup()
        { }

        #region 用户分组
        /// <summary>
        /// 判断用户分组是否存在
        /// </summary>
        /// <param name="UG_Name">用户分组名称</param>
        /// <returns></returns>
        public bool UserGroupExists(string Name)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from t_UserGroup");
            strSql.Append(" where UG_Name=@UG_Name ");
            SqlParameter[] parameters = {
					new SqlParameter("@UG_Name", SqlDbType.NVarChar,30)};
            parameters[0].Value = Name;


            return SqlServerHelper.Exists(strSql.ToString(), parameters);
        }

        /// <summary>
        /// 增加一个用户分组
        /// </summary>
        /// <param name="model">用户分组实体类</param>
        /// <returns></returns>
        public int CreateUserGroup(RGP.Model.UserGroup model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into t_UserGroup(");
            strSql.Append("UG_Name,UG_Order,UG_SuperiorID,UG_Depth,UG_Description)");
            strSql.Append(" values (");
            strSql.Append("@UG_Name,@UG_Order,@UG_SuperiorID,@UG_Depth,@UG_Description)");

            SqlParameter[] parameters = {
					new SqlParameter("@UG_Name", SqlDbType.NVarChar,30),
					new SqlParameter("@UG_Order", SqlDbType.Int,4),
                    new SqlParameter("@UG_SuperiorID", SqlDbType.Int,4),
                    new SqlParameter("@UG_Depth", SqlDbType.Int,4),
					new SqlParameter("@UG_Description", SqlDbType.NVarChar,50)};
            parameters[0].Value = model.UG_Name;
            parameters[1].Value = model.UG_Order;
            parameters[2].Value = model.UG_SuperiorID;
            parameters[3].Value = GetUserGroupDepth(model.UG_SuperiorID) + 1;//更新级别深度
            parameters[4].Value = model.UG_Description;

            if (SqlServerHelper.ExecuteSql(strSql.ToString(), parameters) >= 1)
                return 1;
            else
                return 0;
        }

        /// <summary>
        /// 更新一条数据
        /// </summary>
        /// <param name="model">用户分组实体类</param>
        /// <returns></returns>
        public bool UpdateUserGroup(RGP.Model.UserGroup model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update t_UserGroup set ");
            strSql.Append("UG_Name=@UG_Name,");
            strSql.Append("UG_Order=@UG_Order,");
            strSql.Append("UG_SuperiorID=@UG_SuperiorID,");
            strSql.Append("UG_Depth=@UG_Depth,");
            strSql.Append("UG_Description=@UG_Description");
            strSql.Append(" where UG_ID=@UG_ID");
            SqlParameter[] parameters = {
					new SqlParameter("@UG_ID", SqlDbType.Int,4),
					new SqlParameter("@UG_Name", SqlDbType.NVarChar,30),
					new SqlParameter("@UG_Order", SqlDbType.Int,4),
                    new SqlParameter("@UG_SuperiorID", SqlDbType.Int,4),
                    new SqlParameter("@UG_Depth", SqlDbType.Int,4),
					new SqlParameter("@UG_Description", SqlDbType.NVarChar,50)};
            parameters[0].Value = model.UG_ID;
            parameters[1].Value = model.UG_Name;
            parameters[2].Value = model.UG_Order;
            parameters[3].Value = model.UG_SuperiorID;
            parameters[4].Value = GetUserGroupDepth(model.UG_SuperiorID) + 1;//更新级别深度
            parameters[5].Value = model.UG_Description;

            if (SqlServerHelper.ExecuteSql(strSql.ToString(), parameters) >= 1)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 删除用户分组
        /// </summary>
        /// <param name="ID">用户分组ID</param>
        /// <returns></returns>
        public int DeleteUserGroup(int id)
        {
            int ret = 0;
            string strSql1 = "Select UserID from t_users where UserGroup=@UG_ID";
            string strSql2 = "Select UG_ID from t_UserGroup where UG_SuperiorID=@UG_ID";
            string strSql3 = "delete t_UserGroup where UG_ID=@UG_ID";

            SqlParameter[] parameters = {
					new SqlParameter("@UG_ID", SqlDbType.Int,4)};
            parameters[0].Value = id;

            if (!SqlServerHelper.Exists(strSql1, parameters) && !SqlServerHelper.Exists(strSql2, parameters))
            {
                if (SqlServerHelper.ExecuteSql(strSql3, parameters) >= 1)
                {
                    ret = 1;
                }
                else
                {
                    ret = 0;
                }
            }
            else
            {
                ret = 2;
            }
            return ret;
        }

        /// <summary>
        /// 获取模块深度
        /// </summary>
        /// <param name="ID">模块ID</param>
        /// <returns></returns>
        public int GetUserGroupDepth(int id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select UG_Depth from t_UserGroup where UG_ID=@UG_ID");
            SqlParameter[] parameters = {
					new SqlParameter("@UG_ID", SqlDbType.Int,4)};
            parameters[0].Value = id;

            object obj = SqlServerHelper.GetSingle(strSql.ToString(), parameters);
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }

        /// <summary>
        /// 得到一个用户分组实体
        /// </summary>
        /// <param name="ID">用户分组ID</param>
        /// <returns></returns>
        public RGP.Model.UserGroup GetUserGroupModel(int id)
        {
            StringBuilder strSql = new StringBuilder();

            strSql.Append("select top 1 * from t_UserGroup ");
            strSql.Append(" where UG_ID=@UG_ID ");
            SqlParameter[] parameters = {
					new SqlParameter("@UG_ID", SqlDbType.Int,4)};
            parameters[0].Value = id;

            RGP.Model.UserGroup model = new RGP.Model.UserGroup();
            DataSet ds = SqlServerHelper.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                if (ds.Tables[0].Rows[0]["UG_ID"].ToString() != "")
                {
                    model.UG_ID = int.Parse(ds.Tables[0].Rows[0]["UG_ID"].ToString());
                }
                model.UG_Name = ds.Tables[0].Rows[0]["UG_Name"].ToString();
                if (ds.Tables[0].Rows[0]["UG_Order"].ToString() != "")
                {
                    model.UG_Order = int.Parse(ds.Tables[0].Rows[0]["UG_Order"].ToString());
                }
                model.UG_Description = ds.Tables[0].Rows[0]["UG_Description"].ToString();
                if (ds.Tables[0].Rows[0]["UG_Depth"].ToString() != "")
                {
                    model.UG_Depth = int.Parse(ds.Tables[0].Rows[0]["UG_Depth"].ToString());
                }
                if (ds.Tables[0].Rows[0]["UG_SuperiorID"].ToString() != "")
                {
                    model.UG_SuperiorID = int.Parse(ds.Tables[0].Rows[0]["UG_SuperiorID"].ToString());
                }
                if (ds.Tables[0].Rows[0]["UG_Count"].ToString() != "")
                {
                    model.UG_Count = int.Parse(ds.Tables[0].Rows[0]["UG_Count"].ToString());
                }

                return model;
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 获得用户分组数据列表
        /// </summary>
        /// <param name="strWhere">Where条件</param>
        /// <returns></returns>
        public DataSet GetUserGroupList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * FROM t_UserGroup ");

            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            strSql.Append(" order by UG_Order,UG_SuperiorID desc");

            return SqlServerHelper.Query(strSql.ToString());
        }

        /// <summary>
        /// 获得用户分组下级分类
        /// </summary>
        /// <param name="id">分类ID</param>
        /// <returns></returns>
        public DataSet GetChildUserGroupList(int id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * FROM t_UserGroup where UG_SuperiorID=" + id.ToString() + "order by UG_Order asc");
            return SqlServerHelper.Query(strSql.ToString());
        }

        /// <summary>
        /// 判断用户分组下是否有模块
        /// </summary>
        /// <param name="ID">分类iD</param>
        /// <returns></returns>
        public bool IsUser(int id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select UserID from t_users where UG_ID=" + id.ToString());
            if (SqlServerHelper.ExecuteSql(strSql.ToString()) == 0)
            {
                return false;
            }
            else
            {
                return true;
            }

        }

        #endregion
    }
}